package com.augurit.gzcgwsp.qyzz.service.impl;

import java.io.File;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.read.biff.BiffException;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.dozer.DozerBeanMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springside.modules.orm.Page;

import com.augurit.gzcgwsp.excelimportexport.util.ExcelUtil;
import com.augurit.gzcgwsp.qyzz.dao.YwRqQyzzJsRyDao;
import com.augurit.gzcgwsp.qyzz.entity.YwRqQyzzFinalJsRy;
import com.augurit.gzcgwsp.qyzz.entity.YwRqQyzzJsRy;
import com.augurit.gzcgwsp.qyzz.service.IYwRqFinalQyzzJsRyService;
import com.augurit.gzcgwsp.qyzz.service.IYwRqQyzzJsRyService;

/**
 * 
 *  企业资质--企业专业技术人员状况表service实现类
 * @author 张军
 *
 */
@Service
@Transactional
public class YwRqQyzzJsRyServiceImpl  implements IYwRqQyzzJsRyService {
	private final static Log log=LogFactory.getLog(YwRqQyzzJsRyServiceImpl.class);
	 @Autowired
	private YwRqQyzzJsRyDao ywRqQyzzJsDao;
	 @Autowired
	private IYwRqFinalQyzzJsRyService ywRqFinalQyzzJsRyService;
	 
	public void saveEntity(YwRqQyzzJsRy entity) {
		ywRqQyzzJsDao.save(entity);
	}

	/*
	 * 导入企业专业技术人员状况表
	 * @see com.augurit.gzcgwsp.qyzz.service.IYwRqQyzzJsRyService#importQyzzInfo(jxl.Sheet, java.lang.String, long, java.lang.String)
	 */
	public String importQyzzJsRy(Sheet sheet, String acceptNo, long qyid,
			String username,Map map) {
		String errors="";
		String process=(String) map.get("process");
		if("cksa".equals(process)){
//		    List<YwRqQyzzFinalJsRy> list=ywRqFinalQyzzJsRyService.getQyzzJsRy(qyid,acceptNo);
//		    if(list!=null && list.size()>0){
//                for(YwRqQyzzFinalJsRy entity:list){
//                    //先删除
//                    ywRqFinalQyzzJsRyService.delete(entity);
//                }
//            }
		  //新增
            int rows=sheet.getRows();
            for(int i=2;i<rows;i++){
                String name=ExcelUtil.readData(sheet, 0, i);//姓名
                String idCard=ExcelUtil.readData(sheet, 1, i);//身份证号码
                String jszc=ExcelUtil.readData(sheet,2, i);//技术职称
                String zsbh=ExcelUtil.readData(sheet, 3, i);//证书编号
                String fzbm=ExcelUtil.readData(sheet, 4,i);//发证部门
                if(isHasData(name,idCard,zsbh,fzbm)){
                    YwRqQyzzFinalJsRy entity=new YwRqQyzzFinalJsRy();
                    entity.setQyid(qyid);
                    entity.setIdCard(idCard);
                    entity.setZsbh(zsbh);
                    entity.setFzbm(fzbm);
                    entity.setName(name);
                    entity.setJszc(jszc);
                    entity.setUsername(username);
                    try {
                        ywRqFinalQyzzJsRyService.saveEntity(entity);
                    } catch (Exception e) {
                        log.error("第二个工作表"+"第"+i+"行出现错误\n"+e);
                        errors+="第二个工作表"+"第"+i+"行出现错误\n";               
                    }
                }
            }
		    
		}else{
    		List<YwRqQyzzJsRy> list=ywRqQyzzJsDao.getQyzzJsRy(qyid,acceptNo);
    		if(list!=null && list.size()>0){
    			for(YwRqQyzzJsRy entity:list){
    				//先删除
    				ywRqQyzzJsDao.delete(entity);
    			}
    		}
    		//新增
    		int rows=sheet.getRows();
    		for(int i=2;i<rows;i++){
    			String name=ExcelUtil.readData(sheet, 0, i);//姓名
    			String idCard=ExcelUtil.readData(sheet, 1, i);//身份证号码
    			String jszc=ExcelUtil.readData(sheet,2, i);//技术职称
    			String zsbh=ExcelUtil.readData(sheet, 3, i);//证书编号
    			String fzbm=ExcelUtil.readData(sheet, 4,i);//发证部门
    			if(isHasData(name,idCard,zsbh,fzbm)){
    				YwRqQyzzJsRy entity=new YwRqQyzzJsRy();
    				entity.setAcceptNo(acceptNo);//受理编号
    				entity.setQyid(qyid);
    				entity.setIdCard(idCard);
    				entity.setZsbh(zsbh);
    				entity.setFzbm(fzbm);
    				entity.setName(name);
    				entity.setJszc(jszc);
    				entity.setUsername(username);
    				try {
    					ywRqQyzzJsDao.save(entity);
    				} catch (Exception e) {
    					log.error("第二个工作表"+"第"+i+"行出现错误\n"+e);
    					errors+="第二个工作表"+"第"+i+"行出现错误\n";				
    				}
    			}
    		}
		}
		return errors;
	}
	
	/**
	 * 判断是否有数据企业专业技术人员状况表
	 * @param name
	 * @param idCard
	 * @param zsbh
	 * @param fzbm
	 * @return
	 */
	public boolean isHasData(String name,String idCard,String zsbh,String fzbm){
		if(StringUtils.isNotBlank(name)&&StringUtils.isNotBlank(idCard)
			&& StringUtils.isNotBlank(fzbm) )
			return true;
		else 
			return false;
		
	}
	
	
	  public static void main(String[] args) throws BiffException, IOException{
		  String filepath="c:\\安装维修企业资质申请表922.xls";
		   File file=new File(filepath);
			Workbook wb = Workbook.getWorkbook(file);
			Sheet sheet=wb.getSheet(2);
			int rowss=sheet.getRows();
			System.out.print(rowss);
			String dwmc=ExcelUtil.readData(sheet,1, 2);//单位名称
			System.out.print(dwmc);
	   }

	 
	  
	 /*
	  * excel填充数据--企业专业技术人员状况表
	  * @see com.augurit.gzcgwsp.qyzz.service.IYwRqQyzzJsRyService#copyWritableSheet(jxl.write.WritableSheet, java.lang.String, long)
	  */ 
	public void copyWritableSheet(WritableSheet ws, String acceptNo,
			long qyid,String type) {
	    if("qyxx".equals(type)){//查看正式企业信息
	           List<YwRqQyzzFinalJsRy> list=ywRqFinalQyzzJsRyService.getQyzzJsRy(qyid, acceptNo);
	            for(int i=0;i<list.size();i++){
	                try {
	                    YwRqQyzzFinalJsRy entity=list.get(i);
	                    String idcard=entity.getIdCard();//身份证
	                    String zsbh=entity.getZsbh();//证书编号
	                    String fzbm=entity.getFzbm();//发证部门
	                    String name=entity.getName();//姓名
	                    String jszc=entity.getJszc();//技术职称
	                    WritableCellFormat wcf = new WritableCellFormat();
	                    wcf.setBorder(Border.ALL, BorderLineStyle.THIN);
	                    wcf.setVerticalAlignment(VerticalAlignment.CENTRE); 
	                    ExcelUtil.modiStrCell2(ws, 0,  i+2, name, wcf);
	                    ExcelUtil.modiStrCell2(ws, 1,  i+2, idcard, wcf);
	                    ExcelUtil.modiStrCell2(ws, 2,  i+2, jszc, wcf);
	                    ExcelUtil.modiStrCell2(ws, 3,  i+2, zsbh, wcf);
	                    ExcelUtil.modiStrCell2(ws, 4,  i+2, fzbm, wcf);
	                } catch (Exception e) {
	                    log.error("受理编号"+acceptNo+"导出企业专业技术人员状况错误"+e);
	                } 
	            }
	    }else{
    		List<YwRqQyzzJsRy> list=ywRqQyzzJsDao.getQyzzJsRy(qyid,acceptNo);
    		for(int i=0;i<list.size();i++){
    			try {
    				YwRqQyzzJsRy entity=list.get(i);
    				String idcard=entity.getIdCard();//身份证
    				String zsbh=entity.getZsbh();//证书编号
    				String fzbm=entity.getFzbm();//发证部门
    				String name=entity.getName();//姓名
    				String jszc=entity.getJszc();//技术职称
    				WritableCellFormat wcf = new WritableCellFormat();
    	 			wcf.setBorder(Border.ALL, BorderLineStyle.THIN);
    	 			wcf.setVerticalAlignment(VerticalAlignment.CENTRE); 
    				ExcelUtil.modiStrCell2(ws, 0,  i+2, name, wcf);
    				ExcelUtil.modiStrCell2(ws, 1,  i+2, idcard, wcf);
    				ExcelUtil.modiStrCell2(ws, 2,  i+2, jszc, wcf);
    				ExcelUtil.modiStrCell2(ws, 3,  i+2, zsbh, wcf);
    				ExcelUtil.modiStrCell2(ws, 4,  i+2, fzbm, wcf);
    			} catch (Exception e) {
    				log.error("受理编号"+acceptNo+"导出企业专业技术人员状况错误"+e);
    			} 
    		}
	   }
	}

    public Map importFinalData(Long qyid, String acceptNo) {
        List<YwRqQyzzJsRy> list=this.ywRqQyzzJsDao.getQyzzJsRy(qyid, acceptNo);
        for(YwRqQyzzJsRy entity:list){
            YwRqQyzzFinalJsRy finalEntity = new DozerBeanMapper().map(entity, YwRqQyzzFinalJsRy.class);
            finalEntity.setId(null);
            this.ywRqFinalQyzzJsRyService.saveEntity(finalEntity);
        }
        return null;
    }

    /*
     * (non-Javadoc)
     * @see com.augurit.gzcgwsp.qyzz.service.IYwRqQyzzJsRyService#getPersons(java.lang.String, java.lang.String, java.lang.Long)
     */
	public List<Long> getPersons(String name, String idCard, Long qyid) {
		String sql="select qyid from " +YwRqQyzzJsRy.class.getName()+
		" where name='"+name+"'" +" and idCard='"+idCard+"'"+" and qyid!="+qyid;
		return this.ywRqQyzzJsDao.find(sql);
	}

	/*
	 * 分页查询(non-Javadoc)
	 * @see com.augurit.gzcgwsp.qyzz.service.IYwRqQyzzJsRyService#search(org.springside.modules.orm.Page, java.util.Map)
	 */
	public Page<YwRqQyzzJsRy> search(Page<YwRqQyzzJsRy> page, Map map) {
		String acceptNo=(String) map.get("acceptNo");
		String hql=" from "+YwRqQyzzJsRy.class.getName()+" where acceptNo='"+acceptNo+"'";
		Map<String,String> values=new HashMap<String,String>();
		values.put("acceptNo", acceptNo);
		page=ywRqQyzzJsDao.findPage(page, hql, values);
		return page;
	}

	/*
	 * (non-Javadoc)
	 * @see com.augurit.gzcgwsp.qyzz.service.IYwRqQyzzJsRyService#getPage(java.lang.String, org.springside.modules.orm.Page, java.lang.Long, java.lang.String)
	 */
	public Page getPage(String acceptNo, Page page,
			Long qyid, String type) {
		String hql=" from "+YwRqQyzzJsRy.class.getName()+" where acceptNo='"+acceptNo+"'";
		if("2".equals(type)){
			hql=" from "+YwRqQyzzFinalJsRy.class.getName()+" where qyid='"+qyid+"'";
		}
		Map<String,String> values=new HashMap<String,String>();
		page=ywRqQyzzJsDao.findPage(page, hql, values);
		return page;
	}
	
	 
}
